Note

How to create spatial index

CREATE INDEX some_descriptive_idx_name
ON table_name
USING GIST (geometry);

Note:

The USING GIST clause tells PostgreSQL to use the generic index structure (GIST) when building the index. If you receive an error that looks like ERROR: index row requires 11340 bytes, maximum size is 8191 when creating your index, you have likely neglected to add the USING GIST clause.

Import data

Import OpenStreetMap

Import districts

Import airbnb

Check that data and geospatial attributes are correct w.r.t. coordinate system used

Check data & test that spatial queries work

Test query: filter roads / airbnb data

10km radius

Filter roads to Sydney (approximate)

Filter Airbnb listings to Sydney (approximate)

make sure all coordinate systems are consistent

save

undo

Create test data and load to PostGIS, and save as geojson

Create test roads data

518k roads in NSW

Create test listings data

Check results

MongoDB

Insert data

Test Geospatial operators

$nearSphere

$nearSphere + $maxDistance, $centerSphere

https://www.latlong.net/place/sydney-opera-house-australia-3894.html

$geoIntersects / $geoWithin

Note

Spatial join is not possible in mongo db. In other words, using $geoWithin or $geoIntersects as part of $lookup pipeline in aggregation is not possible.

$expr is needed to access the variable that stores geometry of the subject that is performing the $lookup, but $geoIntersects or $geoWithin are not aggregation operators. Only $geoNear can be used in aggregation pipeline.

geoWithin

geoIntersects

Performance Comparison

What's being tested

PostGIS MongoDB
ST_Within() $geoWithin
ST_Intersects() $geoIntersects

Operations

Note: The discrepancies in the results between PostGIS and Mongo is due to rounding. PostGIS uses 5 decimal places for lon/lat while MongoDB uses 6.

Select a polygon to use as a constant

Query 1: Intersection

PostGIS

ST_Intersects()

SELECT *
FROM roads
WHERE ST_Intersects(roads.geometry, ST_GeometryFromText(text WKT, SRID));

Example: ST_GeomFromText('POINT(-126.4 45.32)', 312)

MongoDB

Query 1 Result

Query 2: Within

PostGIS

ST_Within()

SELECT *
FROM a
WHERE ST_Within(a.geometry, b.geometry);
-- Evaluates to True if a is inside of b

MongoDB

Query 2 Result

ST_Relates

Cross intersection

T-intersection